USE [Test_GCCS]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetSources]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spGetSources]
GO

CREATE PROCEDURE [dbo].[spGetSources]
AS
	DECLARE @RPDatabase VARCHAR(MAX) = '[DATA-SRV-01].DSRSystem'
	DECLARE @EventsDatabase VARCHAR(MAX) = 'Test_Events'	

	DECLARE @PrincipleEventIndicators TABLE
	(
		ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
		EventIndicatorId UNIQUEIDENTIFIER NOT NULL,
		CompanyId UNIQUEIDENTIFIER NOT NULL,
		PrincipleTemplateId INT NOT NULL,
		PrincipleAssessment INT NOT NULL,
		BaseEventIndicatorId UNIQUEIDENTIFIER NULL,
		IssueType NVARCHAR(MAX) NOT NULL,
		EventIndicatorAssessment INT NOT NULL
	)
	INSERT INTO @PrincipleEventIndicators
	SELECT	dbo.GccsEventIndicators.Id AS EventIndicatorId,
			dbo.GccsCompanyProfiles.CompanyId,
			dbo.GccsPrincipleTemplates.Id AS PrincipleTemplateId, 
			Principles.Assessment AS PrincipleAssessment, 
			dbo.GccsEventIndicators.RpBaseEventIndicatorId AS BaseEventIndicatorId,
			dbo.GccsEventIndicators.IssueType,
			dbo.GccsEventIndicators.Assessment AS EventIndicatorAssessment
	FROM    dbo.GccsPrinciples AS Principles INNER JOIN
            dbo.GccsPillars ON Principles.PillarId = dbo.GccsPillars.Id INNER JOIN
            dbo.GccsCompanyProfiles ON dbo.GccsPillars.CompanyProfilerId = dbo.GccsCompanyProfiles.Id INNER JOIN
            dbo.GccsPrincipleTemplates ON Principles.TemplateId = dbo.GccsPrincipleTemplates.Id INNER JOIN
            dbo.GccsEventIndicators ON Principles.Id = dbo.GccsEventIndicators.PrincipleId INNER JOIN
            dbo.GccsPillarTemplates ON dbo.GccsPillars.TemplateId = dbo.GccsPillarTemplates.Id
	WHERE  (dbo.GccsCompanyProfiles.OverallCompanyAssessment = 1 OR dbo.GccsCompanyProfiles.OverallCompanyAssessment = 2) -- 1 = Watchlist or 2 = NonCompliant
	   AND (dbo.GccsCompanyProfiles.GccsWorkflowStatus = 3) -- Final
	   AND (dbo.GccsEventIndicators.Assessment = 1 OR dbo.GccsEventIndicators.Assessment = 2)

	DECLARE @XML XML
	DECLARE @SelectedCompanies NVARCHAR(MAX)
	DECLARE @SelectedEventIndicators NVARCHAR(MAX)

	DECLARE @Companies TABLE
	(
		Id UNIQUEIDENTIFIER NOT NULL
	)
	INSERT INTO @Companies
	SELECT DISTINCT CompanyId FROM @PrincipleEventIndicators

	SET @XML = (SELECT * FROM @Companies FOR XML RAW('Company'), ROOT('Companies'), ELEMENTS, TYPE)
	SET @SelectedCompanies = CONVERT(NVARCHAR(MAX), @XML, 1)

	DECLARE @EventIndicators TABLE
	(
		c UNIQUEIDENTIFIER NOT NULL,
		bei UNIQUEIDENTIFIER NOT NULL,
		eia INT NOT NULL
	)
	INSERT INTO @EventIndicators
	SELECT CompanyId, BaseEventIndicatorId, EventIndicatorAssessment FROM @PrincipleEventIndicators
	WHERE BaseEventIndicatorId IS NOT NULL
	
	SET @XML = (SELECT * FROM @EventIndicators FOR XML RAW('ei'), ROOT('eis'), ELEMENTS, TYPE)
	SET @SelectedEventIndicators = CONVERT(NVARCHAR(MAX), @XML, 1)

	DECLARE @CurrentQuarterText NVARCHAR(MAX)
	SET @CurrentQuarterText = FORMAT(GETDATE(), 'yyyy-MM-dd')

	DECLARE @Sources TABLE
	(
		CompanyId UNIQUEIDENTIFIER NOT NULL,
		Quarter DATETIME NOT NULL,
		Type INT NOT NULL,
		EventIndicatorAssessment INT NOT NULL,
		PublicationDate DATETIME NOT NULL,
		NewsArticleName NVARCHAR(MAX) NOT NULL,
		SourceName NVARCHAR(MAX) NULL
	)
	INSERT INTO @Sources
	EXEC(@RPDatabase + '.dbo.spGetSourcesForGC ''' + @SelectedCompanies + ''', ''' + @CurrentQuarterText + '''')

	INSERT INTO @Sources
	EXEC(@EventsDatabase + '.dbo.spGetNewsArticleSources ''' + @SelectedEventIndicators + ''', ''' + @CurrentQuarterText + '''')
	
	SELECT CompanyId, Type, EventIndicatorAssessment, PublicationDate, NewsArticleName, SourceName FROM @Sources

RETURN 0

GO


